package com.longe.parse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;

/**
 * 解析excel
 */
public class ExcelParse {

    private final static String excel2003L =".xls";    //2003版本的excel

    private final static String excel2007U =".xlsx";  //2007版本的excel

    private final static String ERROR_NULL = "Upload error,the %s row %s field is null";

    private final static String ERROR_CONVERT = "Upload error,the %s row %s field convert error";

    private final static String ERROR_FORMAT = "Upload error,upload excel format error";

    /**
     * Excel表头对应Entity属性 解析封装javabean
     * @param clazz      entity对应的class对象
     * @param in         excel流
     * @param fileName   文件名
     * @param checkFields   excel中需要校验的列
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> parseExcel(Class<T> clazz, InputStream in, String fileName, Set<String> checkFields) throws Exception {
        checkFile(fileName);    //是否EXCEL文件
        Workbook workbook = getWorkBoot(in, fileName); //兼容新老版本
        List<T> excelForBeans = readExcel(clazz, workbook, checkFields);  //解析Excel
        return excelForBeans;
    }


    /**
     * 校验是否是Excel文件
     * @param fileName
     * @throws Exception
     */
    public static void checkFile(String fileName) throws Exception {
        if (!fileName.endsWith(excel2007U) && !fileName.endsWith(excel2003L)) {
            throw new RuntimeException(ERROR_FORMAT);
        }
    }

    /**
     * 兼容新老版Excel
     * @param in
     * @param fileName
     * @return
     * @throws IOException
     */
    private static Workbook getWorkBoot(InputStream in, String fileName) throws IOException {
        if (fileName.endsWith(excel2007U)) {
            return new XSSFWorkbook(in);
        } else {
            return new HSSFWorkbook(in);
        }
    }

    /**
     * 解析Excel
     * @param clazz    类的class对象
     * @param workbook   工作簿对象
     * @param <T>
     * @return
     * @throws Exception
     */
    private static <T> List<T> readExcel(Class<T> clazz, Workbook workbook, Set<String> checkFields) throws Exception {
        List<T> instanceList = new ArrayList<>();

        Sheet currentSheet = workbook.getSheetAt(0);

        Row firstRow = currentSheet.getRow(currentSheet.getFirstRowNum());
        if (null == firstRow){
            return instanceList;
        }

        //第一行是标题,所以每个cell都不能为空
        if(isAnyCellEmpty(firstRow)) {
            return instanceList;
        }

        Map<String, Field> fieldMap = getFieldMap(clazz.getDeclaredFields());
        for (int rowIndex = currentSheet.getFirstRowNum() + 1; rowIndex <= currentSheet.getLastRowNum(); rowIndex++) {
            Row currentRow = currentSheet.getRow(rowIndex);
            if (null == currentRow) {
                continue;
            }
            if(isAllCellEmpty(currentRow)) {
                continue;
            }
            checkFields(currentRow, rowIndex, firstRow, checkFields);

            T instance = clazz.newInstance();
            for (int cellIndex = firstRow.getFirstCellNum(); cellIndex < firstRow.getLastCellNum(); cellIndex++) {
                Cell currentCell = currentRow.getCell(cellIndex);
                if(null == currentCell){
                    continue;
                }

                String fieldName = firstRow.getCell(cellIndex).getStringCellValue().trim();
                Field field = fieldMap.get(fieldName);
                if(null == field){
                    continue;
                }

                String methodName = MethodUtils.setMethodName(field.getName());
                Method method = clazz.getMethod(methodName, field.getType());
                if (isDateFiled(field)) {
                    /**这里Excel中该cell的类型必须为日期类型的
                     * 如果该cell的类型为文本类型，则需要先设置为CellType.STRING，再getStringCellValue()后用DateFormat来转换**/
                    Date date = currentCell.getDateCellValue();
                    method.invoke(instance, date);
                } else {
                    /**这里不管是什么类型的数据，先设定为String类型的，后续再根据实际情况来处理**/
                    currentCell.setCellType(CellType.STRING);
                    String value = currentCell.getStringCellValue();
                    /**字段转换错误*/
                    Object o;
                    try {
                        o = convertType(field.getType(), value.trim());
                    } catch (Exception e) {
                        throw new RuntimeException(String.format(ERROR_CONVERT, (++rowIndex), field.getName()));
                    }
                    method.invoke(instance, o);
                }
            }
            String methodName = MethodUtils.setMethodName("currentRowNum");
            Method currentRowNumMethod = clazz.getMethod(methodName, fieldMap.get("currentRowNum").getType());
            currentRowNumMethod.invoke(instance, rowIndex + 1);
            instanceList.add(instance);
        }
        return instanceList;
    }

    private static Map<String, Field> getFieldMap(Field[] fields) {
        Map<String, Field> fieldMap = new HashMap<>();
        for (Field field : fields) {
            fieldMap.put(field.getName(), field);
        }
        return fieldMap;
    }

    /**
     * 是否日期字段
     *
     * @param field
     * @return
     */
    private static boolean isDateFiled(Field field) {
        return (Date.class == field.getType());
    }

    /**
     * 类型转换
     * @param clazz
     * @param value
     * @return
     */
    private static Object convertType(Class clazz, String value) {
        if (Integer.class == clazz || int.class == clazz) {
            return Integer.valueOf(value);
        }
        if (Short.class == clazz || short.class == clazz) {
            return Short.valueOf(value);
        }
        if (Byte.class == clazz || byte.class == clazz) {
            return Byte.valueOf(value);
        }
        if (Character.class == clazz || char.class == clazz) {
            return value.charAt(0);
        }
        if (Long.class == clazz || long.class == clazz) {
            return Long.valueOf(value);
        }
        if (Float.class == clazz || float.class == clazz) {
            return Float.valueOf(value);
        }
        if (Double.class == clazz || double.class == clazz) {
            return Double.valueOf(value);
        }
        if (Boolean.class == clazz || boolean.class == clazz) {
            return Boolean.valueOf(value.toLowerCase());
        }
        if (BigDecimal.class == clazz) {
            return new BigDecimal(value);
        }
        return value;
    }

    /**
     * 必填字段做校验
     * @param currentRow 当前行
     * @param rowNum 行号
     * @param firstRow 首行
     * @param checkFields 待校验的字段
     * @return
     */
    private static void checkFields(Row currentRow, int rowNum, Row firstRow, Set<String> checkFields) {
        for (int i = 0; i < currentRow.getLastCellNum(); i++) {
            String filedName = firstRow.getCell(i).getStringCellValue().trim();
            if(!checkFields.contains(filedName)){
                continue;
            }
            Cell cell = currentRow.getCell(i);
            if (null == cell) {
                throw new RuntimeException(String.format(ERROR_NULL, (rowNum + 1), filedName));
            }
            cell.setCellType(CellType.STRING);
            if (StringUtils.isBlank(cell.getStringCellValue().trim())) {
                throw new RuntimeException(String.format(ERROR_NULL, (rowNum + 1), filedName));
            }
        }
    }

    /**
     * 判读当前Row中是否有空的cell
     * @param currentRow 当前行
     * @return
     */
    private static boolean isAnyCellEmpty(Row currentRow) {
        for (int i = 0; i < currentRow.getLastCellNum(); i++) {
            Cell cell = currentRow.getCell(i);
            if (null == cell || cell.getCellType() == CellType.BLANK){
                return true;
            }
            cell.setCellType(CellType.STRING);
            if (StringUtils.isBlank(cell.getStringCellValue().trim())){
                return true;
            }
        }
        return false;
    }

    /**
     * 判读当前Row中所有cell都为空
     * @param currentRow 当前行
     * @return
     */
    private static boolean isAllCellEmpty(Row currentRow) {
        for (int i = 0; i < currentRow.getLastCellNum(); i++) {
            Cell cell = currentRow.getCell(i);
            if(null == cell){
                continue;
            }
            cell.setCellType(CellType.STRING);
            if (null != cell && cell.getCellType() != CellType.BLANK && StringUtils.isNotBlank(cell.getStringCellValue().trim())){
                return false;
            }
        }
        return true;
    }
    /**
     * 获取properties的set方法
     */
    static class MethodUtils {
        private static final String SET_PREFIX = "set";
        private static String capitalize(String name) {
            if (name == null || name.length() == 0) {
                return name;
            }
            return name.substring(0, 1).toUpperCase() + name.substring(1);
        }
        public static String setMethodName(String propertyName) {
            return SET_PREFIX + capitalize(propertyName);
        }

    }
}
